﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Data.SqlClient;
using System.Configuration;
using System.Data;

namespace UygulamaYetkiService
{
    public class GrupDAO : IDisposable
    {
        public Grup GrupKaydet(Grup grup)
        {
            using (SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["TakbisBilgiSorguYetkiConnection"].ToString()))
            {
                using (SqlCommand cmd = new SqlCommand())
                {
                    con.Open();
                    SqlTransaction transaction = con.BeginTransaction("trans");
                    try
                    {
                        cmd.CommandText = @"insert into grup with(updlock) (Adi,Aciklama,KayitTarih) values (@Adi,@Aciklama,getdate()); SELECT SCOPE_IDENTITY()";
                        cmd.Parameters.Add("@Adi", grup.Adi);
                        cmd.Parameters.Add("@Aciklama", grup.Aciklama);
                        cmd.Connection = con;
                        cmd.Transaction = transaction;
                        grup.ID = Convert.ToInt64(cmd.ExecuteScalar());
                        transaction.Commit();
                    }
                    catch (Exception ex)
                    {
                        transaction.Rollback();
                        using (ExceptionLogDao el = new ExceptionLogDao())
                        {
                            el.HataLogYaz(DateTime.Now.ToString() + "  " + ex.ToString());
                        }
                    }
                    finally
                    {
                        con.Close();
                        con.Dispose();
                        cmd.Dispose();
                    }
                    return grup;
                }
            }
        }

        public Grup GrupGuncelle(Grup grup)
        {
            using (SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["TakbisBilgiSorguYetkiConnection"].ToString()))
            {
                using (SqlCommand cmd = new SqlCommand())
                {
                    con.Open();
                    SqlTransaction transaction = con.BeginTransaction("trans");
                    try
                    {
                        cmd.CommandText = @"update grup with(updlock) set Adi=@Adi,Aciklama=@Aciklama,KayitTarih=getdate() where ID=@ID";
                        cmd.Parameters.Add("@Adi", grup.Adi);
                        cmd.Parameters.Add("@Aciklama", grup.Aciklama);
                        cmd.Parameters.Add("@ID", grup.ID);
                        cmd.Connection = con;
                        cmd.Transaction = transaction;
                        cmd.ExecuteScalar();
                        transaction.Commit();
                    }
                    catch (Exception ex)
                    {
                        transaction.Rollback();
                        using (ExceptionLogDao el = new ExceptionLogDao())
                        {
                            el.HataLogYaz(DateTime.Now.ToString() + "  " + ex.ToString());
                        }
                    }
                    finally
                    {
                        con.Close();
                        con.Dispose();
                        cmd.Dispose();
                    }
                    return grup;
                }
            }
        }

        public bool GrupSil(Grup grup)
        {
            bool sonuc = false;
            using (SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["TakbisBilgiSorguYetkiConnection"].ToString()))
            {
                using (SqlCommand cmd = new SqlCommand())
                {
                    con.Open();
                    SqlTransaction transaction = con.BeginTransaction("trans");
                    try
                    {
                        cmd.CommandText = @"delete from grup where ID=@ID  ";
                        cmd.Parameters.Add("@ID", grup.ID);
                        cmd.Connection = con;
                        cmd.Transaction = transaction;
                        cmd.ExecuteScalar();
                        transaction.Commit();
                        sonuc = true;
                    }
                    catch (Exception ex)
                    {
                        transaction.Rollback();
                        using (ExceptionLogDao el = new ExceptionLogDao())
                        {
                            el.HataLogYaz(DateTime.Now.ToString() + "  " + ex.ToString());
                        }
                    }
                    finally
                    {
                        con.Close();
                        con.Dispose();
                        cmd.Dispose();
                    }
                    return sonuc;
                }
            }
        }

        public List<Grup> GrupGetir()
        {
            List<Grup> grupList = null;
            try
            {
                using (SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["TakbisBilgiSorguYetkiConnection"].ToString()))
                {
                    string sql = string.Format( @"select ID,Adi,Aciklama,KayitTarih from grup with(nolock) order by Adi");
                    using (SqlCommand cmd = new SqlCommand(sql))
                    {
                        using (SqlDataAdapter sda = new SqlDataAdapter())
                        {
                            cmd.Connection = con;
                            sda.SelectCommand = cmd;
                            cmd.CommandTimeout = 60000;
                            DataTable dt = new DataTable();
                            sda.Fill(dt);
                            if (dt.Rows.Count > 0)
                            {
                                grupList = new List<Grup>();
                                foreach (DataRow dr in dt.Rows)
                                {
                                    Grup grup = new Grup(dr);
                                    grupList.Add(grup);
                                }
                            }
                        }
                    }
                }
            }
            catch (Exception ex)
            {
                using (ExceptionLogDao el = new ExceptionLogDao())
                {
                    el.HataLogYaz(DateTime.Now.ToString() + "  " + ex.ToString());
                }
            }
            return grupList;
        }

        public Grup GrupGetirbyID(long id)
        {
            Grup grup = null;
            try
            {
                using (SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["TakbisBilgiSorguYetkiConnection"].ToString()))
                {
                    string sql = @"select ID,Adi,Aciklama,KayitTarih from grup with(nolock) where ID=@ID order by Adi";
                    using (SqlCommand cmd = new SqlCommand(sql))
                    {
                        using (SqlDataAdapter sda = new SqlDataAdapter())
                        {
                            cmd.Connection = con;
                            cmd.Parameters.Add("@ID", id);
                            sda.SelectCommand = cmd;
                            cmd.CommandTimeout = 60000;
                            DataTable dt = new DataTable();
                            sda.Fill(dt);
                            if (dt.Rows.Count == 1)
                                grup = new Grup(dt.Rows[0]);
                        }
                    }
                }
            }
            catch (Exception ex)
            {
                using (ExceptionLogDao el = new ExceptionLogDao())
                {
                    el.HataLogYaz(DateTime.Now.ToString() + "  " + ex.ToString());
                }
            }
            return grup;
        }

        public bool GrupVarMiKontrol(string ad)
        {
            bool sonuc = false;
            try
            {
                using (SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["TakbisBilgiSorguYetkiConnection"].ToString()))
                {
                    string sql = @"select ID,Adi,Aciklama,KayitTarih from grup with(nolock) where Adi=@Adi order by Adi";
                    using (SqlCommand cmd = new SqlCommand(sql))
                    {
                        using (SqlDataAdapter sda = new SqlDataAdapter())
                        {
                            cmd.Connection = con;
                            cmd.Parameters.Add("@Adi", ad);
                            sda.SelectCommand = cmd;
                            cmd.CommandTimeout = 60000;
                            DataTable dt = new DataTable();
                            sda.Fill(dt);
                            if (dt.Rows.Count > 0)
                                sonuc = true;
                        }
                    }
                }
            }
            catch (Exception ex)
            {
                using (ExceptionLogDao el = new ExceptionLogDao())
                {
                    el.HataLogYaz(DateTime.Now.ToString() + "  " + ex.ToString());
                }
            }
            return sonuc;
        }

        public void Dispose()
        {
        }
    }
}
